t_xml_orders

bulk_order_id		NUMBER 	composite PK


t_oss_ds

t_products

product_id

t_orders

order_id		number(4)		PK

t_settlements

<></>

pk


fk



NUMBER
CHAR
VARCHAR
LONG
BLOB
DATE
TIMESTAMP
CLOB
BFILE
XMLTYPE






t_Employee - EmpID, EmpName, DeptID, DOJ , CTC
t_Department - DeptID ,DeptName, HODID
t_EmployeeLeaveDetail - EmpID , Leavedate, ApprovedBy,ApprovedDate.


select 

	e2.empname as HODNAME,
	e1.empname as ename	
from
	t_employee e1,	t_employee e2, t_department d
where
	d.deptid = e2.dept_id and
	d.hodid = e1.empid;




with e as
(select 
	d.deptname, 
	e.ename, 
	e.doj, 
	row_number(partition by d.deptname order by e.doj asc) as rk
from
	t_employee e, t_department d
where 
	e.deptid = d.deptid)

select * from e where rk = 1;






select *
from t_employee e1
where E1.empid not in (select E2.empid 
			from t_EmployeeLeaveDetail e2
			where e1.empid = e2.empid
			and to_char(leavedate,'YYYY') = to_char(sysdate,'YYYY')




select
	d.deptname, count(nvl(e.empname,-1)),
from
	t_employee e full outer join t_department d
on
	e.deptid = d.deptid
group by
	d.deptname;